﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsServiceConfig
{
    public partial class Form1 : Form
    {
        //数据库连接
        SQLiteConnection m_dbConnection;
        public Form1()
        {
            InitializeComponent();
        }
        //创建一个空的数据库
        void createNewDatabase()
        {
            if (File.Exists(@"MyDatabase.sqlite"))
            {
                //MessageBox.Show("文件存在");
            }
            else
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
            }

        }

        //创建一个连接到指定数据库
        void connectToDatabase()
        {
            m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            m_dbConnection.Open();
        }

        //在指定数据库中创建一个table
        void createTable()
        {
            //SQLiteCommand mDbCmd = m_dbConnection.CreateCommand();
            string sql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='highscores';";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            if (0 == Convert.ToInt32(command.ExecuteScalar()))
            {
                sql = "create table highscores (name varchar(20), score int)";
                command = new SQLiteCommand(sql, m_dbConnection);
                command.ExecuteNonQuery();
            }
            else
            {
                //table - Student does exist.
            }

        }

        //插入一些数据
        void fillTable()
        {
            string sql = "insert into highscores (name, score) values ('Me', 3000)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

            sql = "insert into highscores (name, score) values ('Myself', 6000)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();

            sql = "insert into highscores (name, score) values ('And I', 9001)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }

        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            string sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
            Console.ReadLine();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            createNewDatabase();
            connectToDatabase();
            createTable();
            fillTable();
            printHighscores();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            m_dbConnection.Open();
            m_dbConnection.ChangePassword("111");//设置密码
            m_dbConnection.Close();
        }
    }
}
